package com.etc.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import javax.annotation.Resource;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import com.etc.dao.StudentDao;
import com.etc.entity.Students;
import com.etc.util.MyDBUtil;
import com.etc.util.PageData;

@Repository(value = "studao")
public class StudentDaoImpl implements StudentDao {
	@Resource
	private JdbcTemplate jdbcTemplate;

	@Resource(name = "dbutil")
	private MyDBUtil dbutli;

	@Resource(name = "pagedate")
	private PageData<Students> pd;

	/**
	 * 查询所有
	 */
	@Override
	public List<Students> getStudent() {
		// 调用jdbcTemplate的query方法得到所有学生对象的集合
		List<Students> list = jdbcTemplate.query("select * from t_student", new RowMapper<Students>() {
			@Override
			public Students mapRow(ResultSet arg0, int arg1) throws SQLException {
				Students student = new Students();
				student.setSid(arg0.getInt("sid"));
				student.setStudentname(arg0.getString("studentname"));
				student.setStudentsex(arg0.getString("studentsex"));
				student.setStudentage(arg0.getInt("studentage"));
				return student;
			}
		});
		return list;
	}

	@Override
	public Students getStudentById(int sid) {
		// 调用jdbcTemplate的queryForObject方法得到单个学生对象，参数为sid
		Students student = jdbcTemplate.queryForObject("select * from t_student where sid = ?",
				new RowMapper<Students>() {
					@Override
					public Students mapRow(ResultSet arg0, int arg1) throws SQLException {
						Students student = new Students();
						student.setSid(arg0.getInt("sid"));
						student.setStudentname(arg0.getString("studentname"));
						student.setStudentsex(arg0.getString("studentsex"));
						student.setStudentage(arg0.getInt("studentage"));
						return student;
					}
				}, sid);
		return student;
	}

	@Override
	public PageData<Students> getStudentLimit(int page, int limit, String content) {
		
		// 调用jdbcTemplate的query方法得到所有学生对象的集合
		String sql = "select * from t_student where studentname like ?";// where studentname like ?
		String selSql = "select count(1) from (" + sql + ") t";
		int count = dbutli.getCount(selSql,content);//,content
		int start = (page - 1) * limit;
		selSql = sql + " limit " + start + "," + limit;
		// String newsql = "select * from" + "(" + selSql + ")" + "as temp";
		List<Students> list = jdbcTemplate.query(selSql, new RowMapper<Students>() {
			@Override
			public Students mapRow(ResultSet arg0, int arg1) throws SQLException {
				Students student = new Students();
				student.setSid(arg0.getInt("sid"));
				student.setStudentname(arg0.getString("studentname"));
				student.setStudentsex(arg0.getString("studentsex"));
				student.setStudentage(arg0.getInt("studentage"));
				return student;
			}
		}, "%" + content + "%");// 有个content问题, "%" + content + "%"
		
		// PageData data = dbutli.getPage("select * from t_student", page, limit,
		// Students.class,list, "%" + content + "%");
		pd.setLimit(limit);
		pd.setData(list);
		pd.setPage(page);
		pd.setCount(count);
		return pd;
	}

}
